﻿Public Class clsProdInfo
    Public Function getProdInfoById(prod_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT a.*,b.material_code FROM sf.b_prod_info a left join sf.b_flb_info b on a.flb_sys_id = b.sys_id WHERE 1 = 1 and a.factory_id = ? and a.prod_id = ? "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, "TS", prod_id)
        Return dt
    End Function

    Public Function getProdInfoById(prod_id As String, status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_prod_info WHERE 1 = 1 and factory_id = ? and prod_id = ? and status = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, "TS", prod_id, status)
        Return dt
    End Function

    Public Function getLHList() As DataTable
        Dim szSql As String = String.Empty
        szSql &= "select a.*,b.mes_project_name,c.name as in_vu_bz_name from sf.b_prod_info a "
        szSql &= " left join sf.b_pgd_lh_details b on a.b_pgd_lh_details_sys_id = b.sys_id "
        szSql &= " left join sf.t_mst_bz c on a.in_vu_bz = c.sys_id "
        szSql &= " where 1=1 "
        szSql &= "and cast(a.status as integer) = 5 "
        szSql &= " order by into_vu_time "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        Return dt
    End Function

    Public Function intoLHJ(prod_id As String, lhj As String, tzgb_lh_wt As Decimal,
                            oper As String, bz As Integer,
                            lh_pgd_id As String, pgd_code As String, prod_spec_id As String, flb_sys_id As String, flb_dl_no As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql = String.Empty
            szSql &= "select * from sf.t_mst_mj_details where lhj_id = ? "
            Dim dtrtas As DataTable = DBacs.ExecuteSql(szSql, -1, lhj)
            If dtrtas.Rows.Count = 1 Then
                szSql = String.Empty
                szSql &= "update sf.b_prod_info set update_time=(SELECT CURRENT_TIMESTAMP(6)),"
                szSql &= "status=?,lhj_id=?,tzgb_lh_wt=?,into_vu_time =(SELECT CURRENT_TIMESTAMP(6)) ,in_vu_ope=?,in_vu_bz=?,mould_no=?,b_pgd_lh_details_sys_id=?,flb_up_sys_id=?,flb_up_dl_no=?,yj_status = ?"
                szSql &= "where factory_id = ? and prod_id = ?"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "5", lhj, tzgb_lh_wt, oper, bz, dtrtas.Rows(0)("mould_no").ToString(), lh_pgd_id, flb_sys_id, flb_dl_no, "未检查", "TS", prod_id)
            Else
                szSql = String.Empty
                szSql &= "update sf.b_prod_info set update_time=(SELECT CURRENT_TIMESTAMP(6)),"
                szSql &= "status=?,lhj_id=?,tzgb_lh_wt=?,into_vu_time =(SELECT CURRENT_TIMESTAMP(6)) ,in_vu_ope=?,in_vu_bz=?, b_pgd_lh_details_sys_id=?,flb_up_sys_id=?,flb_up_dl_no=?,yj_status = ?"
                szSql &= "where factory_id = ? and prod_id = ?"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "5", lhj, tzgb_lh_wt, oper, bz, lh_pgd_id, flb_sys_id, flb_dl_no, "未检查", "TS", prod_id)
            End If
            szSql = String.Empty
            szSql &= "insert into sf.b_prod_pgd_info(factory_id,prod_id,pgd_id,workshop_id)"
            szSql &= " values ('TS','" & prod_id & "','" & pgd_code & "','3')"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1)
            szSql = String.Empty
            szSql &= " UPDATE sf.t_hw_setting "
            szSql &= " SET status = ? , prod_id = ?, prod_spec_id = ?,last_minutes = ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            szSql &= " WHERE "
            szSql &= " factory_id = ? and workshop_id = ? and equip_type_id = ? and equip_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", prod_id, prod_spec_id, 0, "TS", "LH", "LHJ", lhj)
            szSql = String.Empty
            szSql &= "select * from sf.b_pgd_lh_details where sys_id = ? "
            Dim dtrt As DataTable = DBacs.ExecuteSql(szSql, -1, lh_pgd_id)
            If dtrt.Rows.Count > 0 Then
                If (dtrt.Rows(0)("oper_count") - dtrt.Rows(0)("lhj_finish_count")) > 0 Then
                    '更新生产计划
                    szSql = String.Empty
                    szSql &= "update sf.b_pgd_lh_details set lhj_finish_count = lhj_finish_count + 1 where sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, lh_pgd_id)
                    szSql = String.Empty
                    szSql &= "update sf.b_pgd_mes set finish_count = finish_count + 1 where sys_id = ? "
                    rtn = DBacs.ExecuteUpdateSql(szSql, -1, dtrt.Rows(0)("b_pgd_mes_sys_id").ToString())
                End If
            End If

            '修改法兰板、吊篮状态为已使用
            szSql = String.Empty
            szSql &= "update sf.b_flb_info set used_piece_num=used_piece_num+1,status=?,used_date=(SELECT CURRENT_TIMESTAMP(6)),update_time=(SELECT CURRENT_TIMESTAMP(6))"
            szSql &= "where sys_id=?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", flb_sys_id)

            szSql = String.Empty
            szSql &= "select * from sf.b_flb_info where sys_id= ?"
            Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, flb_sys_id)
            szSql = String.Empty
            szSql &= "update sf.t_mst_dl set status = ? where dl_no = ? "
            If dt.Rows(0)("piece_num") = dt.Rows(0)("used_piece_num") Then
                '更新法兰板吊篮主档忙闲字段
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", flb_dl_no)
                '修改法兰板、吊篮状态为已使用
                szSql = String.Empty
                szSql &= "update sf.b_flb_info set status=?,used_date=(SELECT CURRENT_TIMESTAMP(6)),update_time=(SELECT CURRENT_TIMESTAMP(6))"
                szSql &= "where sys_id=?"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "2", flb_sys_id)
            ElseIf Int32.Parse(dt.Rows(0)("piece_num")) - Int32.Parse(dt.Rows(0)("used_piece_num")) = 1 Then
                '更新法兰板吊篮主档忙闲字段
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "2", flb_dl_no)
            End If

            szSql = String.Empty
            szSql &= "select * from sf.b_flb_ck where dl_no = ? and status = ? "
            Dim dtflbck As DataTable = DBacs.ExecuteSql(szSql, -1, flb_dl_no, "0")
            If dtflbck.Rows.Count = 1 Then
                szSql = String.Empty
                szSql &= "update sf.b_flb_ck set status = ?, end_date = (SELECT CURRENT_TIMESTAMP(6)),out_ope = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) where sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", oper, dtflbck.Rows(0)("sys_id"))
            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function changeLHJ(prod_id As String, prod_spec_id As String, old_lhj As String, new_lhj As String)
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql = String.Empty
            szSql &= "select * from sf.t_mst_mj_details where lhj_id = ? "
            Dim dtrtas As DataTable = DBacs.ExecuteSql(szSql, -1, new_lhj)
            If dtrtas.Rows.Count = 1 Then
                szSql = String.Empty
                szSql &= "update sf.b_prod_info set lhj_id=?,mould_no=? where factory_id = ? and prod_id = ?"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, new_lhj, dtrtas.Rows(0)("mould_no").ToString(), "TS", prod_id)
            Else
                szSql = String.Empty
                szSql &= "update sf.b_prod_info set lhj_id=? where factory_id = ? and prod_id = ?"
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, new_lhj, "TS", prod_id)
            End If
            szSql = String.Empty
            szSql &= " UPDATE sf.t_hw_setting "
            szSql &= " SET status = ?, prod_id = ?, prod_spec_id = ?,last_minutes = ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            szSql &= " WHERE "
            szSql &= " factory_id = ? and workshop_id = ? and equip_type_id = ? and equip_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "1", prod_id, prod_spec_id, 0, "TS", "LH", "LHJ", new_lhj)
            szSql = String.Empty
            szSql &= " UPDATE sf.t_hw_setting "
            szSql &= " SET status = ?, prod_id = ?, prod_spec_id = ?,last_minutes = ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            szSql &= " WHERE "
            szSql &= " factory_id = ? and workshop_id = ? and equip_type_id = ? and equip_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", String.Empty, String.Empty, 0, "TS", "LH", "LHJ", old_lhj)
            szSql = String.Empty
            szSql &= " UPDATE sf.b_lhj_work_detail SET lhj_id = ? where prod_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, new_lhj, prod_id)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function yjStatus(prod_id As String, yj_status As String) As Integer
        Dim szSql As String = String.Empty
        szSql &= "update sf.b_prod_info set update_time=(SELECT CURRENT_TIMESTAMP(6)), yj_status = ? "
        szSql &= " where factory_id = ? and prod_id = ?"
        Return DBacs.ExecuteUpdateSql(szSql, -1, yj_status, "TS", prod_id)
    End Function

    Public Function getyjStatus(prod_id As String) As DataTable
        Dim szSql As String = "SELECT yj_status FROM sf.b_prod_info WHERE 1 = 1 and factory_id = ? and prod_id = ? "
        Return DBacs.ExecuteSql(szSql, -1, "TS", prod_id)
    End Function

    Public Function outLHJ(prod_id As String, lhj As String, oper As String, bz As Integer, yjzl As Decimal) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            '变更制品的状态信息（出炉时间，出炉作业员)
            szSql = String.Empty
            szSql &= "update sf.b_prod_info set update_time=(SELECT CURRENT_TIMESTAMP(6)),"
            szSql &= " status=?,comp_time =(SELECT CURRENT_TIMESTAMP(6)),out_vu_ope = ?,out_vu_bz = ?, yj_wt = ? "
            szSql &= " where factory_id = ? and prod_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "6", oper, bz, yjzl, "TS", prod_id)

            szSql = String.Empty
            szSql &= " UPDATE sf.t_hw_setting "
            szSql &= " SET status = ? , prod_id = ? ,prod_spec_id = ? ,last_minutes = ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            szSql &= " WHERE factory_id = ? and workshop_id = ? and equip_type_id = ? and equip_id = ? "
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, "0", "", "", 0, "TS", "LH", "LHJ", lhj)

            szSql = String.Empty
            szSql &= "select * from sf.b_prod_info where factory_id = ? and prod_id = ? "
            Dim dtrt As DataTable = DBacs.ExecuteSql(szSql, -1, "TS", prod_id)
            If dtrt.Rows.Count > 0 Then
                'If (dtrt.Rows(0)("oper_count") - dtrt.Rows(0)("lhj_finish_count")) > 0 Then
                '更新生产计划
                szSql = String.Empty
                szSql &= "update sf.b_pgd_lh_details set lhj_finish_count_out = lhj_finish_count_out + 1 where sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, dtrt.Rows(0)("b_pgd_lh_details_sys_id").ToString())

                'szSql = String.Empty
                'szSql &= "select * from sf.b_pgd_lh_details where factory_id = ? and sys_id = ? "
                'Dim dtrtx As DataTable = DBacs.ExecuteSql(szSql, -1, "TS", dtrt.Rows(0)("b_pgd_lh_details_sys_id").ToString())

                'End If
            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

End Class
